﻿<%@ WebHandler Language="C#" Class="FileHandle" %>

using System;
using System.Web;
using System.Data.SqlClient;
using System.Text;
public class FileHandle : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        #region 判断访问权限        
        if (context.Request.ServerVariables["HTTP_Referer"] == null || context.Request.ServerVariables["REMOTE_HOST"] == null || context.Request.ServerVariables["SERVER_NAME"] != context.Request.Url.Host)
        {
            context.Response.Write("非法访问!");
            context.Response.End();
            return;
        }
        #endregion
        
        
        string action = context.Request.QueryString["ac"];
        switch (action)
        {
            case "renametb": Renametb(context); break;//修改数据表名称.
            case "changedesc": ChangeDbDesc(context); break;//修改说明
            case "altercols": AlterColumns(context); break;//修改表的多个字段
        }
    }

    #region 修改版表的多个字段
    private void AlterColumns(HttpContext context)
    {
        string str = context.Request.Form["str"];
        string tablename = context.Request.Form["tbname"];
        string[] colstr = str.Split(new char[]{'^'},StringSplitOptions.RemoveEmptyEntries);
        StringBuilder sql=new StringBuilder();
        foreach (string item in colstr)
        {
            string[] arrcol=item.Split('|');
            string colname = arrcol[0].Split('$')[0];
            string oldname = arrcol[0].Split('$')[1];
            
                   
            string coltype = arrcol[1].Split('$')[0];            
            string oldcoltype = arrcol[1].Split('$')[1];

            string collength = arrcol[2].Split('$')[0];
            string oldcollength = arrcol[2].Split('$')[1];

            string allowdbnull = arrcol[3].Split('$')[0];
            string oldallowdbnull = arrcol[3].Split('$')[1];

            string ispk = arrcol[4].Split('$')[0];
            string oldispk = arrcol[4].Split('$')[1];

            string isindex = arrcol[5].Split('$')[0];
            string oldisindex = arrcol[5].Split('$')[1];

            string defaultval = arrcol[6].Split('$')[0];
            string olddefaultval = arrcol[6].Split('$')[1];

            string brief =context.Server.UrlDecode(arrcol[7].Split('$')[0]);
            string oldbrief =context.Server.UrlDecode(arrcol[7].Split('$')[1]);

            string px = arrcol[8].Split('$')[0];
            string oldpx = arrcol[8].Split('$')[1];        
            if (!string.IsNullOrEmpty(oldname))
            {
                //修改表
                if (oldname != colname)
                {
                   //修改表名称
                    sql.Append("  sp_rename '"+tablename+"."+oldname+"','"+colname+"','column'");
                }
                if(!string.Equals(coltype.ToLower(),oldcoltype.ToLower()))//修改列类型
                {
                    sql.Append(" alter table [" + tablename + "] alter column ["+colname+"] ");
                }
                if ("nvarchar,varchar,char".Contains(coltype.ToLower()))
                {
                    sql.Append(" " + coltype + "(" + collength + ")");
                }
                else
                {
                    sql.Append(" " + coltype);
                }
                if (string.Equals(allowdbnull,"0"))
                {
                    sql.Append(" not null");
                }
                if (!string.IsNullOrEmpty(defaultval))
                {
                    sql.Append(" default '" + defaultval + "'");
                }
                if (!string.IsNullOrEmpty(brief) && oldbrief!=brief)
                {
                    sql.Append(" EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + brief + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + tablename + "', @level2type=N'COLUMN',@level2name=N'" + colname + "'");
                }
            }
            else//添加新列
            {
                sql .Append( " alter   table   [" + tablename + "]   add   [" + colname + "]  ");
                if ("nvarchar,varchar,char".Contains(coltype.ToLower()))
                {
                    sql .Append( " " + coltype + "(" + collength + ")");
                }
                else
                {
                    sql .Append( " " + coltype);               
                }
                if(!string.IsNullOrEmpty(defaultval))
                {
                    if ("nvarchar,varchar,char".Contains(coltype.ToLower()))
                    {
                        sql.Append(" default '" + defaultval + "'");
                    }
                    else
                    {
                        sql.Append(" default " + defaultval + "");
                    }
                }
                if (!string.IsNullOrEmpty(brief))
                {
                    sql .Append( " EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + brief + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'"+tablename+"', @level2type=N'COLUMN',@level2name=N'"+colname+"'");
                }
            }
            
            //检测列是否存在
            if (DbHelperSQL.ColumnExists(tablename, colname))
            {
                //sql+=string.Format(" alter table [{0}] alter column [{1}];
                
            }
            else//新增列
            {
                
            }
        }      
        try
        {            
            DbHelperSQL.ExecuteSql(sql.ToString());            
            context.Response.Write("ok");
        }
        catch(Exception ex)
        {
            context.Response.Write(ex.Message);
        }
    }
    #endregion
    private void Renametb(HttpContext context)
    {
        string old = context.Request.Form["oldname"];
        string newname = context.Request.Form["newname"];
        try
        {
            string sql = "EXEC sp_rename '" + old + "','" + newname + "'";
            DbHelperSQL.ExecuteSql(sql);

            context.Response.Write("ok");
        }
        catch
        {
            context.Response.Write("error");
        }
    }
    private void ChangeDbDesc(HttpContext context)
    {

        string tablename = context.Request.Form["tbname"];
        string description = context.Request.Form["description"];
        try
        {
            string sql = " EXECUTE sp_addextendedproperty N'MS_Description',@description, N'user', N'dbo', N'table', N'" + tablename + "', NULL, NULL";
            SqlParameter[] paras =
            {
                new SqlParameter("@description",description)
            };
            DbHelperSQL.ExecuteSql(sql, paras);
            context.Response.Write("ok");
        }
        catch
        {
            context.Response.Write("error");
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}